﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using Ebiz.libs;
using System.Data;
using System.Data.SqlClient;

namespace Ebiz
{
    public partial class DownloadRQ : System.Web.UI.Page
    {
        public string strTest = "";
        public string _ReqStat = "";

        string _ReqStatus = "";
        string _Period = "";
        string _DateStart = "";
        string _DateEnd = "";
        string _Distributor = "";
        string _ReqName = "";
        string _ReqProduct = "";
        string _ReqCustomer = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack) {
                if (!(Request.Form == null)){
                    
                    _ReqStatus = Request.Form["FilterBy[Status]"];
                    _Period = Request.Form["FilterBy[Period]"];
                    _DateStart = Request.Form["FilterBy[DateStart]"];
                    _DateEnd = Request.Form["FilterBy[DateEnd]"];
                    _Distributor = Request.Form["FilterBy[Distributor]"];
                    _ReqName = Request.Form["FilterBy[ReqNames]"];
                    _ReqProduct = Request.Form["FilterBy[ReqProducts]"];
                    _ReqCustomer = Request.Form["FilterBy[ReqCustomers]"];
                }
                ExportDoc();
                //ShowList();
            }
        }

        public override void VerifyRenderingInServerForm(Control control){

        }

        protected void OnPageIndexChanging(object sender, GridViewPageEventArgs e){
            tblCL.PageIndex = e.NewPageIndex;
            this.BindTable();
        }
        
        private void BindTable() {
            string qry = "SELECT * FROM UserAccounts";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataAdapter da = new SqlDataAdapter(qry, cn);
                    DataSet ds = new DataSet("dSet");
                    da.Fill(ds);

                    tblCL.DataSource = ds;
                    tblCL.DataBind();
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }

        private void ExportDoc() {
            string FLName = "Ebiz_ReqList_" + DateTime.Today.ToString("ddMMyy") + ".xls";
            //Response.ClearContent();
            Response.Clear();
            Response.Buffer = true;
            Response.AppendHeader("content-disposition", "attachment;filename=" + FLName);
            Response.Charset = "UTF-8";
            Response.ContentType = "application/vnd.ms-excel";
            //Response.ContentType = "application/vnd.openxmlformats";
            //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            using(StringWriter stringWriter = new StringWriter()){
                //StringWriter stringWriter = new StringWriter();
                HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

                tblCL.AllowPaging = false;
                //this.BindTable();
                this.ShowList();

                tblCL.HeaderRow.BackColor = System.Drawing.Color.White;
                tblCL.HeaderRow.BorderStyle = BorderStyle.None;
                foreach (TableCell tblCell in tblCL.HeaderRow.Cells) {
                    //tblCell.Style["background-color"] = "#5D7B9D";
                    tblCell.BackColor = tblCL.HeaderStyle.BackColor;
                    tblCell.BorderStyle = BorderStyle.Solid;
                    tblCell.Style["border-width"] = "0.5pt";
                    tblCell.Style["border-color"] = "#CCFFFF";
                }
                foreach (GridViewRow gvRow in tblCL.Rows) {
                    gvRow.BackColor = System.Drawing.Color.White;
                    foreach (TableCell gvTblCell in gvRow.Cells) {
                        gvTblCell.BorderStyle = BorderStyle.Solid;
                        gvTblCell.Style["border-width"] = "0.5pt";
                        gvTblCell.BorderColor = System.Drawing.Color.Black;
                        if(gvRow.RowIndex % 2 == 0){
                            gvTblCell.Style["background-color"] = "#F7F6F3";
                        }else{
                            gvTblCell.Style["background-color"] = "#FFFFFF";
                        }
                    }
                }
                tblCL.RenderControl(htmlTextWriter);
                Response.Write(stringWriter.ToString());
                Response.Flush();
                Response.End();
            }
        }
        
        private void ShowList(){
            string ReqStatus = "";
            string Period = "";
            string Distributor = "";
            string ReqName = "";
            string ReqProduct = "";
            string ReqCustomer = "";

            Dictionary<string, object> dctParams = new Dictionary<string, object>();
            if (!(string.IsNullOrEmpty(_ReqStatus) || _ReqStatus == "0")){
                //ReqStatus = " AND (tbl_Request.StatusCode = @StatusCode)";
                //dctParams.Add("@StatusCode", GetStatusCode(_ReqStatus));
                ReqStatus = " AND (tbl_Request.StatusCode IN " + GetStatusCode(_ReqStatus) + ")";
            }
            if (string.IsNullOrEmpty(_Period) || _Period == "0"){
                string DateStart = "";
                string DateEnd = "";
                if (!(string.IsNullOrEmpty(_DateStart) || _DateStart == "0")){
                    DateStart = " AND (tbl_Request.DateRequest >= (CONVERT(datetime,@DateStart,0)))";
                    dctParams.Add("@DateStart", Func.FormatDate(_DateStart, Func.DTFormat.DMYtoYMD, "-", "/"));
                }
                if (!(string.IsNullOrEmpty(_DateEnd) || _DateEnd == "0")){
                    DateEnd = " AND (tbl_Request.DateRequest <= (CONVERT(datetime,@DateEnd,0)))";
                    dctParams.Add("@DateEnd", Func.FormatDate(_DateEnd, Func.DTFormat.DMYtoYMD, "-", "/"));
                }
                Period = DateStart + DateEnd;
            }
            if (!(string.IsNullOrEmpty(_Distributor) || _Distributor == "" || _Distributor == "0")){
                Distributor = " AND (tbl_Request.DistributorCode = @DistributorCode)";
                dctParams.Add("@DistributorCode", _Distributor);
            }
            if (!(string.IsNullOrEmpty(_ReqName) || _ReqName == "0")){
                string[] tmpReqName = _ReqName.Split('.');
                int ReqNameLen = tmpReqName.Length;
                if (ReqNameLen > 1){
                    ReqName = " AND (tbl_Request.CreatedBy IN (" + GetParamValues(_ReqName) + "))";
                }else{
                    ReqName = " AND (tbl_Request.CreatedBy = @Requesters)";
                    dctParams.Add("@Requesters", _ReqName);
                }
            }
            if (!(string.IsNullOrEmpty(_ReqProduct) || _ReqProduct == "0")){
                string[] tmpReqProduct = _ReqProduct.Split(',');
                int ReqProductLen = tmpReqProduct.Length;
                if (ReqProductLen > 1){
                    ReqProduct = " AND (tbl_RequestProduct.ProductCode IN (" + GetParamValues(_ReqProduct) + "))";
                }else{
                    ReqProduct = " AND (tbl_RequestProduct.ProductCode = @ProductCode)";
                    dctParams.Add("@ProductCode", _ReqProduct);
                }
            }
            if (!(string.IsNullOrEmpty(_ReqCustomer) || _ReqCustomer == "0")){
                string[] tmpReqCustomer = _ReqCustomer.Split(',');
                int ReqCustomerLen = tmpReqCustomer.Length;
                if (ReqCustomerLen > 1){
                    ReqCustomer = " AND (tbl_RequestCustomer.CSCodeAZI IN (" + GetParamValues(_ReqCustomer) + "))";
                }else{
                    ReqCustomer = " AND (tbl_RequestCustomer.CSCodeAZI = @CustomerCode)";
                    dctParams.Add("@CustomerCode", _ReqCustomer);
                }
            }

            string QryWhere = "";
            int dctLen = dctParams.Count;
            QryWhere = ReqStatus + Period + Distributor + ReqName + ReqProduct + ReqCustomer;

            string dbName = DBUtilities.GetDBName("devCon");
            /*
            string qry = "SELECT ROW_NUMBER() OVER (ORDER BY tbl_Request.CLNum) AS Num, " +
            "tbl_Request.CLNum, tbl_Request.CreatedBy AS UserID, ReqField.FullName AS Requester, " +
            "convert(varchar, tbl_Request.DateRequest, 105) AS DateRequest, " +
            "tbl_Request.DistributorCode, tbl_Request.CLCustName AS CustomerName, " +
            "(SELECT UserAccounts.FullName FROM " + dbName + ".dbo.UserAccounts " +
            "WHERE (UserAccounts.UserName = tbl_Request.ApprovalCurrent)) AS NextApproval, " +
            "mstStatusCode.Description AS CLStatus, tbl_Request.Token  " +
            "FROM " + dbName + ".dbo.tbl_Request " +
            "INNER JOIN " + dbName + ".dbo.mstStatusCode ON mstStatusCode.StatusCode = tbl_Request.StatusCode " +
            "INNER JOIN " + dbName + ".dbo.UserAccounts AS ReqField ON ReqField.UserName = tbl_Request.CreatedBy " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestProduct ON tbl_RequestProduct.CLNum = tbl_Request.CLNum " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestCustomer ON tbl_RequestCustomer.CLNum = tbl_Request.CLNum " +
            "WHERE (tbl_Request.StatusCode IS NOT NULL)" + QryWhere +
            " GROUP BY tbl_Request.CLNum, tbl_Request.CreatedBy, tbl_Request.DateRequest, " +
            "ReqField.FullName, tbl_Request.DistributorCode, " +
            "tbl_Request.CLCustName, tbl_Request.ApprovalCurrent, mstStatusCode.Description, tbl_Request.Token";
            */
            string qry = "SELECT ROW_NUMBER() OVER (ORDER BY tbl_Request.DateRequest DESC, " +
            "tbl_Request.CLNum DESC) AS Num, " +
            "tbl_Request.CLNum, tbl_Request.CreatedBy AS UserID, ReqField.FullName AS Requester, " +
            "convert(varchar, tbl_Request.DateRequest, 105) AS DateRequest, " +
            "tbl_Request.DistributorCode, tbl_Request.CLCustName AS CustomerName, " +
            "(SELECT UserAccounts.FullName FROM " + dbName + ".dbo.UserAccounts " +
            "WHERE (UserAccounts.UserName = tbl_Request.ApprovalCurrent) AND " +
            "((tbl_Request.StatusCode = '200') OR (tbl_Request.StatusCode = '210'))) AS NextApproval, " +
            "mstStatusCode.Description AS CLStatus, tbl_Request.Token, tbl_Request.StatusCode  " +
            "FROM " + dbName + ".dbo.tbl_Request " +
            "INNER JOIN " + dbName + ".dbo.mstStatusCode ON mstStatusCode.StatusCode = tbl_Request.StatusCode " +
            "INNER JOIN " + dbName + ".dbo.UserAccounts AS ReqField ON ReqField.UserName = tbl_Request.CreatedBy " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestProduct ON tbl_RequestProduct.CLNum = tbl_Request.CLNum " +
            "INNER JOIN " + dbName + ".dbo.tbl_RequestCustomer ON tbl_RequestCustomer.CLNum = tbl_Request.CLNum " +
            " WHERE (tbl_Request.StatusCode IS NOT NULL)" + QryWhere +
            " GROUP BY tbl_Request.CLNum, tbl_Request.CreatedBy, tbl_Request.DateRequest, " +
            "ReqField.FullName, tbl_Request.DistributorCode, " +
            "tbl_Request.CLCustName, tbl_Request.ApprovalCurrent, mstStatusCode.Description, " +
            "tbl_Request.Token, tbl_Request.StatusCode";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    //if (dctLen > 0){
                        foreach (KeyValuePair<string, object> kvp in dctParams){
                            cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                        }
                    //}
                    //SqlDataAdapter da = new SqlDataAdapter(qry, cn);
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    DataSet ds = new DataSet("dSet");
                    da.Fill(ds);
                    if (ds.Tables[0].Rows.Count > 0){
                        tblCL.DataSource = ds;
                        tblCL.DataBind();
                    }else{
                        Response.End();
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
        }
        
        private static string GetStatusCode(string StatusCode){
            string result = "";
            switch (StatusCode){
                case "1":
                    result = "('200','210')";
                    break;
                case "2":
                    result = "('201','211')";
                    break;
                case "3":
                    result = "('203','213')";
                    break;
                case "4":
                    result = "('204','214')";
                    break;
            }
            return result;
        }

        private static string ShowStatusCode(string StatusCode){
            string result = "";
            switch (StatusCode){
                case "200":
                    result = "Waiting for Approval";
                    break;
                case "201":
                    result = "Approved";
                    break;
                case "203":
                    result = "Return for Information";
                    break;
                case "204":
                    result = "Rejected";
                    break;
                case "210":
                    result = "Waiting for Approval (Bulk)";
                    break;
                case "211":
                    result = "Approved (Bulk)";
                    break;
                case "213":
                    result = "Return for Information (Bulk)";
                    break;
                case "214":
                    result = "Rejected (Bulk)";
                    break;
            }
            return result;
        }

        private static string GetRequesterQuery(string Requester){
            string result = "";
            string[] ReqList = Requester.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                result = " AND (tbl_Request.CreatedBy IN (@Requesters)";
            }else{
                result = " AND (tbl_Request.CreatedBy = @Requesters)";
            }
            return result;
        }

        private static string GetProductQuery(string Product){
            string result = "";
            string[] ReqList = Product.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                result = " AND (tbl_RequestProduct.ProductCode IN (@Products)";
            }else{
                result = " AND (tbl_RequestProduct.ProductCode = @Products)";
            }
            return result;
        }

        private static string GetCustomerQuery(string Customer){
            string result = "";
            string[] ReqList = Customer.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                result = " AND (tbl_RequestCustomer.CSCodeAZI IN (@Customers)";
            }else{
                result = " AND (tbl_RequestCustomer.CSCodeAZI = @Customers)";
            }
            return result;
        }

        private static string GetParamValues(string InputValues){
            string result = "";
            string[] ReqList = InputValues.Split(',');
            int ReqCount = ReqList.Length;
            if (ReqCount > 1){
                int idx = 0;
                foreach (string strVals in ReqList){
                    idx++;
                    string tmpResult = (idx < ReqCount) ? "'" + strVals + "'," : "'" + strVals + "'";
                    //result += "'" + strVals + "'";
                    result += tmpResult;
                }
            }else{
                result = InputValues;
            }

            return result;
        }

        private static string GetReqFullName(string Requester){
            string result = "";
            string qry = "SELECT FullName FROM UserAccounts WHERE " +
                "(UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", Requester);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows)
                    {
                        if (rdr.Read())
                        {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportDoc();
        } 
    }
}
